const db = require("../common/db/index")

module.exports = {
  // 获取统计数据
  getStatistics() {
    return new Promise(async (resolve, reject) => {
      try {
        // 获取文章总数
        const articleTotal = await db.query(`SELECT count(*) as total FROM pre_article`)

        // 按分类统计文章数量
        const articleByCate = await db.query(`
                    SELECT cate, count(*) as count 
                    FROM pre_article 
                    GROUP BY cate
                `)

        // 获取用户总数
        const userTotal = await db.query(`SELECT count(*) as total FROM pre_user WHERE role = '0'`)

        // 按性别统计用户数量
        const userByGender = await db.query(`
                    SELECT 
                        CASE 
                            WHEN gender = '男' THEN '男生'
                            WHEN gender = '女' THEN '女生'
                            ELSE '未知性别'
                        END as gender,
                        count(*) as count
                    FROM pre_user 
                    WHERE role = '0'
                    GROUP BY gender
                `)

        // 获取总浏览量
        const totalViews = await db.query(`SELECT SUM(view_count) as total FROM pre_article`)

        // 获取已发布文章数量
        const publishedCount = await db.query(`SELECT count(*) as total FROM pre_article WHERE status = 'published'`)

        // 按分类统计浏览量
        const viewsByCate = await db.query(`
                    SELECT cate, SUM(view_count) as views 
                    FROM pre_article 
                    GROUP BY cate
                `)

        // 获取最近7天的文章发布数量
        const recentArticles = await db.query(`
                    SELECT DATE(createAt) as date, count(*) as count 
                    FROM pre_article 
                    WHERE createAt >= DATE_SUB(NOW(), INTERVAL 7 DAY)
                    GROUP BY DATE(createAt)
                    ORDER BY date DESC
                `)

        resolve({
          code: 200,
          msg: "获取统计数据成功",
          data: {
            articleTotal: articleTotal[0].total || 0,
            userTotal: userTotal[0].total || 0,
            totalViews: totalViews[0].total || 0,
            publishedCount: publishedCount[0].total || 0,
            articleByCate: articleByCate,
            userByGender: userByGender,
            viewsByCate: viewsByCate,
            recentArticles: recentArticles
          }
        })
      } catch (err) {
        reject({
          code: 500,
          msg: "获取统计数据失败",
          err: err
        })
      }
    })
  },
  // 获取公开的基础统计数据（无需token）
  getPublicStatistics() {
    return new Promise(async (resolve, reject) => {
      try {
        // 获取文章总数
        const articleTotal = await db.query(`SELECT count(*) as total FROM pre_article WHERE status = 'published'`)

        // 获取总浏览量
        const totalViews = await db.query(`SELECT SUM(view_count) as total FROM pre_article WHERE status = 'published'`)

        // 按分类统计文章数量
        const articleByCate = await db.query(`
          SELECT cate, count(*) as count 
          FROM pre_article 
          WHERE status = 'published'
          GROUP BY cate
        `)

        resolve({
          code: 200,
          msg: "获取统计数据成功",
          data: {
            articleTotal: articleTotal[0].total || 0,
            totalViews: totalViews[0].total || 0,
            articleByCate: articleByCate
          }
        })
      } catch (err) {
        reject({
          code: 500,
          msg: "获取统计数据失败",
          err: err
        })
      }
    })
  }
}

